# 练习 ：员工表 (EMPLOYEE) 相关操作
# 需求：
# 员工表有 FIRST_NAME VARCHAR(20)，
# LAST_NAME VARCHAR(20), AGE INT, SEX VARCHAR(1), INCOME FLOAT字段
# CREATE TABLE EMPLOYEE(  # 建表语句
#     FIRST_NAME VARCHAR(20),LAST_NAME VARCHAR(20)，
#     AGE INT, SEX VARCHAR(1), INCOME FLOAT
# )
import pymysql
conn = pymysql.connect(  # 创建连接
    host="127.0.0.1", port=3306, user="root",
    passwd="tedu.cn", db="tedu1", charset="utf8"
)
cur = conn.cursor()  # 创建游标
# ------------------使用 PyMySQL 创建该表---------------------
# create_emp = """CREATE TABLE EMPLOYEE(
#     FIRST_NAME VARCHAR(20), LAST_NAME VARCHAR(20),
#     AGE INT, SEX VARCHAR(1), INCOME FLOAT
# )"""
# cur.execute(create_emp)
# -----------------使用 PyMySQL 添加以下三条数据------------------
# insert_emp = "INSERT INTO EMPLOYEE VALUES (%s, %s, %s, %s, %s);"
# cur.executemany(insert_emp, [
#     ('Mac', 'A', 20, 'M', 20000), ('Tom', 'B', 20, 'F', 30000),
#     ('Bob', 'C', 20, 'M', 40000)
# ])
# 'Mac', 'A', 20, 'M', 20000
# 'Tom', 'B', 20, 'F', 30000
# 'Bob', 'C', 20, 'M', 40000
# ------------使用 PyMySQL 查询所有用户信息，并打印结果-------------------------
# select_emp = "SELECT * FROM EMPLOYEE;"
# cur.execute(select_emp)
# print(cur.fetchone())  # 获取单条
# print(cur.fetchmany(2))
# ---------将 FIRST_NAME 为 Mac 的用户工资(INCOME)改成 10000-----------
# update_emp = "UPDATE EMPLOYEE SET INCOME = %s WHERE FIRST_NAME = %s"
# cur.execute(update_emp, (10000, "Mac"))
# ----------删除 FIRST_NAME 为 Tom 的用户信息--------------
delete_emp = "DELETE FROM EMPLOYEE WHERE FIRST_NAME = %s;"
cur.execute(delete_emp, ("Tom", ))
conn.commit()
conn.close()
